from openpyxl import load_workbook
from ExcelUtil import isAlphaDigitPlaceholder, isPureAlphaPlaceholder


# 在数据sheet页（wbDataSheet）
# 的拆分关键字列（splitKeyColumnAlpha）
# 去寻找拆分key（splitKey）
# 如果找到了对应的行
# 然后结合对应的列（columnAlpha）即可找到数据
def getValueFromWbData(wbDataSheet, splitKeyColumnAlpha, splitKey, columnAlpha):
    for i, row in enumerate(wbDataSheet.iter_rows(values_only=True), 1):
        if wbDataSheet[splitKeyColumnAlpha + str(i)].value == splitKey:
            return wbDataSheet[columnAlpha + str(i)].value
    return '未匹配到！'


# 识别模板里面各个sheet
def fillDataToTemplate(splitKey, wbTemplate, wbData, templateDataSheetDict):
    for templateSheetName in wbTemplate.sheetnames:
        wbDataSheetName = templateDataSheetDict[templateSheetName][0]
        splitKeyColumnAlpha = templateDataSheetDict[templateSheetName][1]
        templateSheet = wbTemplate[templateSheetName]
        for row in templateSheet.iter_rows():
            for cell in row:
                if isPureAlphaPlaceholder(cell):
                    dataValue = getValueFromWbData(wbData[wbDataSheetName], splitKeyColumnAlpha, splitKey, cell.value)
                    cell.value = dataValue
                if isAlphaDigitPlaceholder(cell):
                    cell.value = wbData[wbDataSheetName][str(cell.value)].value


def replaceKeyWords(wbTemplate, varDict):
    for templateSheetName in wbTemplate.sheetnames:
        templateSheet = wbTemplate[templateSheetName]
        for row in templateSheet.iter_rows():
            for cell in row:
                if cell is not None and cell.value is not None:
                    for key in varDict:
                        cell.value = str(cell.value).replace(key,str(varDict[key]))


def generateSplitExcelsTemplateModel(dataFileName, templateFileName):
    # 加载工作簿
    wb = load_workbook(filename=templateFileName)
    ws = wb['拆分配置']  # 通过名称选择工作表

    # Step1:解析拆分配置（拆分的key以及拆分后文件存储路径）
    splitKeysStr = ws['B3'].value
    splitKeys = splitKeysStr.split('\n')
    splitPath = ws['B4'].value

    # Step2:解析变量信息
    varDict = {}
    for row in range(3, 50):
        if ws.cell(row=row, column=3) is None \
                or ws.cell(row=row, column=3).value is None \
                or ws.cell(row=row, column=3).value.strip() == '':
            continue
        varDict[ws.cell(row=row, column=4).value] = ws.cell(row=row, column=5).value

    # Step3:解析模板sheet和数据sheet的映射关系
    templateDataSheetDict = {}
    for row in range(3, 50):
        if ws.cell(row=row, column=6) is None \
                or ws.cell(row=row, column=6).value is None \
                or ws.cell(row=row, column=6).value.strip() == '':
            continue
        templateDataSheetDict[ws.cell(row=row, column=6).value] = [ws.cell(row=row, column=7).value,
                                                                   ws.cell(row=row, column=8).value]
    wb.close()

    # Step4:依次打开模板，获取Excel数据，生成批量的拆分结果
    wbData = load_workbook(filename=dataFileName, data_only=True)
    for splitKey in splitKeys:
        savePath = splitPath
        varDict['SPLITKEY'] = splitKey
        # Step4.1:初始化该实例的模板路径
        for var in varDict:
            savePath = savePath.replace(var, str(varDict[var]))
        # Step4.2:打开模板，获取模板中的占位符，然后从数据模板中获取值
        wbTemplate = load_workbook(filename=templateFileName)
        wbTemplate.remove(wbTemplate['拆分配置'])
        replaceKeyWords(wbTemplate, varDict)  # 替换关键字变量
        fillDataToTemplate(splitKey, wbTemplate, wbData, templateDataSheetDict)
        wbTemplate.save(savePath)
        wbTemplate.close()


# 基于行的模型，相当于通过行过滤成为最终的拆分记录，为了保障样式的还原，采用记录匹配位置后，从底向上删除的方式
def generateSplitExcelsRowModel(dataFileName, templateFileName):
    # 加载工作簿
    wb = load_workbook(filename=templateFileName)
    ws = wb['拆分配置']  # 通过名称选择工作表

    # Step1:解析拆分配置（拆分的key以及拆分后文件存储路径）
    splitKeysStr = ws['B3'].value
    splitKeys = splitKeysStr.split('\n')
    splitPath = ws['B4'].value

    # Step2:解析变量信息
    varDict = {}
    for row in range(3, 50):
        if ws.cell(row=row, column=3) is None \
                or ws.cell(row=row, column=3).value is None \
                or ws.cell(row=row, column=3).value.strip() == '':
            continue
        varDict[ws.cell(row=row, column=4).value] = ws.cell(row=row, column=5).value

    # Step3:解析模板sheet和数据sheet的映射关系
    dataSheetDict = {}
    for row in range(3, 50):
        if ws.cell(row=row, column=7) is None \
                or ws.cell(row=row, column=7).value is None \
                or ws.cell(row=row, column=7).value.strip() == '':
            continue
        splitBeginRow = 1
        if ws.cell(row=row, column=9)!=None and ws.cell(row=row, column=9).value!=None:
            splitBeginRow = int(ws.cell(row=row, column=9).value)
        dataSheetDict[ws.cell(row=row, column=7).value] = [ws.cell(row=row, column=7).value,
                                                           ws.cell(row=row, column=8).value,
                                                           splitBeginRow]
    wb.close()

    # Step4:依次打开数据，获取Excel数据，生成批量的拆分结果
    for splitKey in splitKeys:
        wbData = load_workbook(filename=dataFileName, data_only=True)
        savePath = splitPath
        varDict['SPLITKEY'] = splitKey
        # Step4.1:初始化该实例的模板路径
        for var in varDict:
            savePath = savePath.replace(var, str(varDict[var]))
        # Step4.2:找到匹配上的所有行
        for dataSheetName in wbData.sheetnames:
            rowList = []
            if dataSheetName not in dataSheetDict:
                continue
            curSheet = wbData[dataSheetName]
            splitKeyColumnAlpha = dataSheetDict[dataSheetName][1]
            splitBeginRow = dataSheetDict[dataSheetName][2]
            for i, row in enumerate(curSheet.iter_rows(values_only=True), splitBeginRow):
                print(i,row)
                if curSheet[splitKeyColumnAlpha + str(i)].value != splitKey:
                    rowList.append(i)
            for row in sorted(rowList, reverse=True):
                curSheet.delete_rows(row, 1)
        wbData.save(savePath)


def generateSplitExcels(dataFileName, templateFileName):
    # 加载工作簿
    wb = load_workbook(filename=templateFileName)
    ws = wb['拆分配置']  # 通过名称选择工作表
    splitModel = ws['B5'].value
    wb.close()
    if splitModel == '模板模型':
        generateSplitExcelsTemplateModel(dataFileName, templateFileName)
    if splitModel == '行模型':
        generateSplitExcelsRowModel(dataFileName, templateFileName)


if __name__ == '__main__':
    # #############################模板拆分模型#############################
    # dataFileName = 'template_model_data.xlsx'
    # templateFileName = 'template_model_template.xlsx'
    # generateSplitExcels(dataFileName, templateFileName)

    #############################基于行的拆分模型#############################
    dataFileName = 'row_model_data.xlsx'
    templateFileName = 'row_model_template.xlsx'
    generateSplitExcels(dataFileName, templateFileName)
